package dao;

import gui.PublicMethod;

import java.sql.Connection;
import java.sql.Date;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import model.Dorm;
import model.Transition_Student;

public class DaoImp_Comprehensive implements IDao_Comprehensive
{
	private DaoMethod method;
	private Connection conn;

	public DaoImp_Comprehensive()
	{
		conn = DaoMethod.getConnection();
		method = new DaoMethod();
	}

	public List<Transition_Student> queryAll(int scale)
	{
		String exe = null;
		switch (scale)
		{
		case PublicMethod.in:
			exe = "select id,name,dormNumber,date_transition,isout from transition_view where isout=0";
			break;
		case PublicMethod.out:
			exe = "select id,name,dormNumber,date_transition,isout from transition_view where isout=1";
			break;
		case PublicMethod.both:
			exe = "select id,name,dormNumber,date_transition,isout from transition_view";
			break;
		default:
			break;
		}
		return getTransitionList(exe);
	}

	public List<Transition_Student> queryByName(String name, int scale)
	{
		String exe = null;
		switch (scale)
		{
		case PublicMethod.in:
			exe = "select id,name,dormNumber,date_transition,isout from transition_view where name='"
					+ name + "' and isout=0";
			break;
		case PublicMethod.out:
			exe = "select id,name,dormNumber,date_transition,isout from transition_view where name='"
					+ name + "' and isout=1";
			break;
		case PublicMethod.both:
			exe = "select id,name,dormNumber,date_transition,isout from transition_view where name='"
					+ name + "'";
			break;
		default:
			break;
		}
		return getTransitionList(exe);
	}

	public List<Transition_Student> queryByDate(String dateTransition, int scale)
	{
		String exe = null;
		switch (scale)
		{
		case PublicMethod.in:
			exe = "select id,name,dormNumber,date_transition,isout from transition_view where date_transition like '%"
					+ dateTransition + "%' and isout=0";
			break;
		case PublicMethod.out:
			exe = "select id,name,dormNumber,date_transition,isout from transition_view where date_transition like '%'"
					+ dateTransition + "%' and isout=1";
			break;
		case PublicMethod.both:
			exe = "select id,name,dormNumber,date_transition,isout from transition_view where date_transition like '%'"
					+ dateTransition + "%'";
			break;
		default:
			break;
		}
		return getTransitionList(exe);
	}

	public List<Transition_Student> queryByDormNumber(String dormNumber,
			int scale)
	{
		String exe = null;
		switch (scale)
		{
		case PublicMethod.in:
			exe = "select id,name,dormNumber,date_transition,isout from transition_view where dormNumber like '%"
					+ dormNumber + "%' and isout=0";
			break;
		case PublicMethod.out:
			exe = "select id,name,dormNumber,date_transition,isout from transition_view where dormNumber like '%"
					+ dormNumber + "%' and isout=1";
			break;
		case PublicMethod.both:
			exe = "select id,name,dormNumber,date_transition,isout from transition_view where dormNumber like '%"
					+ dormNumber + "%'";
			break;
		default:
			break;
		}
		return getTransitionList(exe);
	}

	private List<Transition_Student> getTransitionList(String exe)
	{
		Statement st = null;
		ResultSet rs = null;
		List<Transition_Student> list = new ArrayList<Transition_Student>();
		try
		{
			st = conn.createStatement();
			rs = st.executeQuery(exe);
			if (rs.next())
			{
				do
				{
					Transition_Student transition = new Transition_Student();
					transition.setId(rs.getInt(1));
					transition.setName(rs.getString(2));
					transition.setDormNUmber(rs.getString(3));
					transition.setDate_transition(rs.getDate(4));
					transition.setIsout(rs.getInt(5));
					list.add(transition);
				}
				while (rs.next());
			}
		}
		catch (SQLException e)
		{
			e.printStackTrace();
			return list;
		}
		finally
		{
			try
			{
				if (rs != null)
				{
					rs.close();
				}
			}
			catch (SQLException e1)
			{
				e1.printStackTrace();
			}
			try
			{
				if (st != null)
				{
					st.close();
				}
			}
			catch (SQLException e)
			{
				e.printStackTrace();
			}
		}
		return list;
	}

}
